﻿Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient


''' <summary>
''' Clase de la capa de acceso a datos DAL que implementa el patrón Table Data Gateway para el manejo de la entidad Familia
''' </summary>
''' <remarks></remarks>
Public Class TDGFamilia

    Private Shared m_Instance As TDGFamilia = Nothing


    Public Function Insert(conn As SqlConnection, Fam_Descrip As String, DVH As Int32, Optional ByRef Fam_Codigo As Int16 = 0) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        Dim outParam_Fam_Codigo As SqlParameter


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.addFamilia"
        command.Parameters.AddWithValue("@Fam_Descrip", Fam_Descrip)
        command.Parameters.AddWithValue("@DVH", DVH)

        outParam_Fam_Codigo = command.Parameters.Add("@Fam_Codigo", SqlDbType.SmallInt)
        outParam_Fam_Codigo.Direction = ParameterDirection.InputOutput



        iRowsAffected = command.ExecuteNonQuery()


        ' Retorno valores
        Fam_Codigo = outParam_Fam_Codigo.Value

        Return iRowsAffected

    End Function

    Public Function Update(conn As SqlConnection, Fam_Codigo As Int16, Fam_Descrip As String, DVH As Int32) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.updFamilia"
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)
        command.Parameters.AddWithValue("@Fam_Descrip", Fam_Descrip)
        command.Parameters.AddWithValue("@DVH", DVH)


        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Delete(conn As SqlConnection, Fam_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.delFamilia"
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Find(conn As SqlConnection, Fam_Codigo As Int16) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader


        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getFamilia"
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)


        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function FindAll(conn As SqlConnection) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getFamilia_All"


        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function Find_Patentes(conn As SqlConnection, Fam_Codigo As Int16) As SqlDataReader
        Dim command As New SqlCommand
        Dim dr As SqlDataReader

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.getFamilia_Patentes"
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)


        dr = command.ExecuteReader()

        Return dr

    End Function

    Public Function Insert_Patente(conn As SqlConnection, _
                                   Fam_Codigo As Int16, _
                                   Pat_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo.addPatente_Familia"
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)
        command.Parameters.AddWithValue("@Pat_Codigo", Pat_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Function Delete_Patente(conn As SqlConnection, Fam_Codigo As Int16, Pat_Codigo As Int16) As Int16
        Dim command As New SqlCommand
        Dim iRowsAffected As Int16

        command.Connection = conn
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "dbo.delPatente_Familia"
        command.Parameters.AddWithValue("@Fam_Codigo", Fam_Codigo)
        command.Parameters.AddWithValue("@Pat_Codigo", Pat_Codigo)

        iRowsAffected = command.ExecuteNonQuery()

        Return iRowsAffected

    End Function

    Public Shared ReadOnly Property Instance()
        Get
            If (m_Instance Is Nothing) Then
                m_Instance = New TDGFamilia()
            End If

            Return m_Instance
        End Get
    End Property

    Private Sub New()

    End Sub

End Class
